package com.xy.config;

public interface CommonSql {

	String jobEntrancePeopleTotal ="SELECT b.exhibition_id exhibitionId,COUNT(0) value,now() date from ( " +
		" SELECT * from oa_general_record  where exhibition_id in ( " +
		" SELECT id  FROM oa_exhibition where close_status = 0) )a " +
		" LEFT JOIN oa_viewer b on a.viewer_id = b.id " +
		" where a.pass_time  >= STR_TO_DATE( now(), '%Y-%m-%d' )   ";

	String jobEntrancePeopleTotalGroupBy ="SELECT count(0) value ,now() date, a.exhibitionId from ( " +
		" SELECT b.exhibition_id exhibitionId,COUNT(0) num,now() date from ( " +
		" SELECT * from oa_general_record  where exhibition_id in ( " +
		" SELECT id  FROM oa_exhibition where close_status = 0) )a " +
		" LEFT JOIN oa_viewer b on a.viewer_id = b.id " +
		" where a.pass_time   >= STR_TO_DATE( now(), '%Y-%m-%d' )  GROUP BY  b.id_card " +
		" )a";

	String jobEntrancePeopleTotalGroupByType =" SELECT c.exhibition_as exhibitionName , b.sort_name sortName,COUNT(0) num ,now() AS date,a.*  from ( " +
		" SELECT b.viewer_type_id,b.exhibition_id exhibitionId  from (" +
		" SELECT * from oa_general_record  where exhibition_id in ( " +
		" SELECT id  FROM oa_exhibition where close_status = 0))a " +
		" LEFT JOIN oa_viewer b on a.viewer_id = b.id " +
		" where a.pass_time    >= STR_TO_DATE( now(), '%Y-%m-%d')  GROUP BY b.exhibition_id, b.id_card,b.viewer_type_id " +
		" )a LEFT JOIN oa_viewer_sort b ON a.viewer_type_id = b.id  LEFT JOIN oa_exhibition c on a.exhibitionId = c.id" +
		" GROUP BY b.exhibition_id, b.sort_name ";

	String jobEntrancePeopleTotalTimeSlot =" SELECT b.quantum time ,IFNULL(a.num,0) num from ( SELECT DATE_FORMAT( create_time, '%H' )time ,COUNT(DATE_FORMAT( create_time, '%H' )) num FROM  oa_general_record  WHERE create_time   >= STR_TO_DATE(now(),'%Y-%m-%d') " +
		"and exhibition_id in (SELECT id from oa_exhibition where close_status =0) GROUP BY DATE_FORMAT( create_time, '%H' )  )a   RIGHT  JOIN oa_quantum b ON a.time = b.quantum ";

	String jobEntrancebackList="SELECT a.*,b.`name`,b.risk_level from ( " +
		" SELECT illicit_viewer,position_name from oa_abnormal_record " +
		"where abnormal_type = 10 AND create_time  >= STR_TO_DATE( now(), '%Y-%m-%d' ) " +
		"AND exhibition_id IN ( SELECT id FROM oa_exhibition WHERE close_status = 0 ) " +
		")a LEFT JOIN oa_blacklist b on a.illicit_viewer = b.id_card";

	String jobEntranceAge="SELECT * FROM (SELECT (case when a.age < 14 THEN '14岁以下'  when 14<=a.age and a.age<=18 THEN '14-18'  when 19<=a.age and a.age<=22 THEN '19-22' " +
		" when 23<=a.age and a.age<=30 THEN '23-30'  when 31<=a.age and a.age<=45 THEN '31-45'  when 46<=a.age and a.age<=60 THEN '46-60' when a.age >= 60 THEN '60岁以上' END ) name ," +
		" (case when a.age < 14 THEN '1'  when 14<=a.age and a.age<=18 THEN '2'  when 19<=a.age and a.age<=22 THEN '3' " +
		" when 23<=a.age and a.age<=30 THEN '4'  when 31<=a.age and a.age<=45 THEN '5'  when 46<=a.age and a.age<=60 THEN '6' " +
		" when a.age >= 60 THEN '7' END ) num ,count(0) value from ( SELECT  b.id_card,YEAR(NOW())-SUBSTR(b.id_card,7,4) age  from (" +
		" SELECT  a.viewer_id FROM oa_general_record a  where  a.exhibition_id in (SELECT id from oa_exhibition where close_status =0 ) and   a.create_time >= STR_TO_DATE(now(),'%Y-%m-%d') GROUP BY a.viewer_id" +
		" )c lEFT JOIN oa_viewer b  ON c.viewer_id = b.id where  LENGTH(b.id_card)=18 GROUP BY b.id_card )a  GROUP BY name) a ORDER BY a.num asc";
}
